﻿using AutoFlatnessTest.Api;
using Dapper;
using Dapper.Contrib.Extensions;
using MiniExcelLibs;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace AutoFlatnessTest.Db
{
    /// <summary>
    /// 测试记录
    /// </summary>
    public class TestRecordDb
    {
        //        public static bool Ini()
        //        {
        //            var path = Path.Combine(LocalFile.DbRecordDir, $"DataCenter{DateTime.Now.Year}.db3");
        //            if (!File.Exists(path))
        //            {
        //                File.WriteAllBytes(path, new byte[] { });

        //                using (var connection = DbHelp.GetSQLiteDataCenterConn())
        //                {
        //                    var co = connection.Execute(@"
        //CREATE TABLE TestRecord(
        //    Id TEXT(32) NOT NULL  , --ID
        //    ProjectId TEXT(32)   , --项目Id
        //    ProjectName TEXT(255)   , --项目名称
        //    GuidId TEXT(32)   , --组ID(同个测试周期)
        //    WorkOrder TEXT(255)   , --工单
        //    RowNum INT   , --行号(原点为右下)
        //    ColumnNum INT   , --列号(原点为右下)
        //    TestNum INT   , --编号(原点为右下)
        //    IsOk INT   , --通过(1通过)
        //    MiSimi DOUBLE(24,6)   , --扫描相似度
        //    MiMaxVal DOUBLE(24,6)   , --扫描最大值
        //    MiMinVal DOUBLE(24,6)   , --扫描最小值
        //    MiVal DOUBLE(24,6)   , --扫描差值
        //    MiInfo TEXT(255)   , --扫描原始信息
        //    MaInfo TEXT(255)   , --扫码原始信息
        //    IsSync INT   , --是否同步(1否)
        //    CreatedTime DATETIME   , --创建时间
        //    PRIMARY KEY (Id)
        //)  ; --测试记录");

        //                }
        //            }

        //            return true;
        //        }

        /// <summary>
        /// 得到所有的可用年份
        /// </summary>
        /// <returns></returns>
        public static List<int> GetNianAll()
        {
            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = @"select distinct strftime('%Y',CreatedTime) as day from TestRecord order by CreatedTime desc";
                return connection.Query<int>(sql).ToList();
            }
        }
        /// <summary>
         /// 得到抽测数量
         /// </summary>
         /// <returns></returns>int
        public static int GetChouChe(string type,string mocode)
        {
            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"select * from TestRecord where Mode='20' and WorkOrder = '{mocode}' and ProjectName='{type}' and CAST(MaInfo AS BLOB) != X'00'  and IsOk = 1 GROUP BY MaInfo";
                return connection.Query<TestRecord>(sql).Count();
            }
        }

        /// <summary>
        /// 得到所有的可用月份
        /// </summary>
        /// <returns></returns>
        public static List<int> GetYueAll(int nian)
        {
            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"select distinct strftime('%m',CreatedTime) as day from TestRecord where CreatedTime>='{nian}-01-01' and CreatedTime<'{nian + 1}-01-01' order by CreatedTime desc";
                return connection.Query<int>(sql).ToList();
            }
        }

        public static List<GetPageDto> GetPage(TestRecordPageDto dto, out int pageCount)
        {
            string where = string.Empty;
            if (dto.TimeGo != null)
                where += " and CreatedTime>@TimeGo";
            if (dto.TimeTo != null)
                where += " and CreatedTime<@TimeTo";
            if (!string.IsNullOrWhiteSpace(dto.Project))
                where += " and ProjectName=@Project";
            if (!string.IsNullOrWhiteSpace(dto.Work))
                where += " and WorkOrder=@Work";
            if (!string.IsNullOrWhiteSpace(dto.Ma))
            {
                dto.Ma = $"%{dto.Ma}%";
                where += " and MaInfo like @Ma";
            }

            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"
select *
from TestRecord
where 1=1{where}
order by CreatedTime desc
{dto.GetPageSql()}
";

                string sql2 = $@"
select count(1)
from TestRecord
where 1=1{where}";

                pageCount = connection.QueryFirstOrDefault<int>(sql2, dto);
                return connection.Query<GetPageDto>(sql, dto).ToList();
            }
        }

        public static bool GetPageExcel(TestRecordPageDto dto, string path)
        {
            string where = string.Empty;
            if (dto.TimeGo != null)
                where += " and CreatedTime>@TimeGo";
            if (dto.TimeTo != null)
                where += " and CreatedTime<@TimeTo";
            if (!string.IsNullOrWhiteSpace(dto.Project))
                where += " and ProjectName=@Project";
            if (!string.IsNullOrWhiteSpace(dto.Work))
                where += " and WorkOrder=@Work";
            if (!string.IsNullOrWhiteSpace(dto.Ma))
            {
                dto.Ma = $"%{dto.Ma}%";
                where += " and MaInfo like @Ma";
            }

            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = $@"
select
GuidId as 组id,
ProjectName as 项目名称,
WorkOrder as 工单,
TestNum as 编号,
RowNum as 行号,
ColumnNum as 列号,
iif(Mode==1,'标准',iif(Mode==10,'录入','')) as 模式,
iif(IsOk==1,'合格','不合格') as 通过,
MiSimi as 扫描相似度,
MiMaxVal as 扫描最大值,
MiMinVal as 扫描最小值,
MiVal as 扫描差值,
MiInfo as 扫描原始信息,
MaInfo as 扫码原始信息,
strftime('%Y-%m-%d %H:%M:%S',CreatedTime) as 生成时间
from TestRecord
where 1=1{where}
order by CreatedTime desc";
                connection.Open();
                var reader = connection.ExecuteReader(sql, dto);
                MiniExcel.SaveAs(path, reader, overwriteFile: true);
                return true;
            }
        }

        /// <summary>
        /// 得到统计图标数据
        /// </summary>
        /// <param name="nian"></param>
        /// <param name="yue"></param>
        /// <returns></returns>
        public static List<TestRecordDayDattaDto> GetDayData(int nian, int yue)
        {
            DateTime dt1 = new DateTime(nian, yue, 1);
            DateTime dt2 = dt1.AddMonths(1);

            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                string sql = @"
select
strftime('%d',CreatedTime) as day,
count(1) count,
count(IsOk==1 or null) okcount,
count(IsOk!=1 or null) nookcount
from TestRecord
where CreatedTime>@dt1 and CreatedTime<@dt2
group by day
order by day";
                return connection.Query<TestRecordDayDattaDto>(sql, new { dt1, dt2 }).ToList();
            }
        }

        public static bool Add(TestRecord testRecord)
        {
            using (var connection = DbHelp.GetSQLiteDataCenterConn())
            {
                testRecord.Id = DbHelp.ExplicitKey;
                testRecord.IsSync = 1;
                testRecord.CreatedTime = DateTime.Now;
                connection.Insert<TestRecord>(testRecord);
            }

            MainWindow.taskTime.AddQueueTask((object obj) =>
            {
                try
                {
                    MainWindow.VModel.SetTbIn();

                    var aaa = (TestRecord)obj;

                    var err = Cmcc.PlAddAsync(aaa).Result;
                    if (!string.IsNullOrEmpty(err))
                    {
                        MainWindow.VModel.SetTbErr(err);
                        return;
                    }

                    using (var connection = DbHelp.GetSQLiteDataCenterConn())
                    {
                        var sql = @"update TestRecord SET IsSync=2 WHERE id=@Id";
                        connection.Execute(sql, new { aaa.Id });
                    }
                }
                catch (Exception ex)
                {
                    MainWindow.VModel.SetTbErr(ex.Message);
                }
                finally
                {
                    MainWindow.VModel.SetTbOk();
                }

            }, testRecord);

            return true;
        }

        public static string UpData()
        {
            try
            {
                var nians = GetNianAll();
                foreach (var nian in nians)
                {
                    using (var connection = DbHelp.GetSQLiteDataCenterConn())
                    {
                        while (true)
                        {
                            string sql = @"select * from TestRecord where IsSync=1 limit 1";
                            var testRecord = connection.QueryFirstOrDefault<TestRecord>(sql);
                            if (testRecord == null)
                                break;

                            var err = Cmcc.PlAddAsync(testRecord).Result;
                            if (!string.IsNullOrEmpty(err))
                            {
                                return err;
                            }

                            sql = @"update TestRecord SET IsSync=2 WHERE id=@Id";
                            connection.Execute(sql, new { testRecord.Id });
                        }

                        while (true)
                        {
                            string sql = @"select * from DemaRecord where IsSync=1 limit 1";
                            var demaRecord = connection.QueryFirstOrDefault<DemaRecord>(sql);
                            if (demaRecord == null)
                                break;

                            var err = Cmcc.PlcheckAddAsync(demaRecord).Result;
                            if (!string.IsNullOrEmpty(err))
                            {
                                return err;
                            }

                            sql = @"update DemaRecord SET IsSync=2 WHERE id=@Id";
                            connection.Execute(sql, new { demaRecord.Id });
                        }
                    }
                }

                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
    }
}
